package com.topisv.tms.service;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.topisv.tms.entity.ExpressBillManage;
import com.topisv.tms.repository.ExpressBillManageDao;
import com.topisv.tms.utils.DateUtils;

/**
 * 快递运单管理
 * @author LiBin
 *
 */
@Component
@Transactional(readOnly = true)
public class ExpressBillManageService {
	private static final Logger log = LoggerFactory.getLogger(ExpressBillManageService.class);
	

	@Autowired
	private ExpressBillManageDao expressBillManageDao;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 开始保存快递单信息
	 * @param customerCode
	 * @param expressBillManages
	 * @param overwrite 是否覆盖原有数据
	 * @return
	 * @throws Exception
	 */
	@Transactional(rollbackFor=Exception.class,readOnly = false)
	public Map<String, List<ExpressBillManage>> save(String customerCode,List<ExpressBillManage> expressBillManages,boolean overwrite) throws Exception{
		Map<String, List<ExpressBillManage>> maps=new HashMap<String, List<ExpressBillManage>>();
		List<ExpressBillManage> normalExpress=new ArrayList<ExpressBillManage>();
		List<ExpressBillManage> exceptionExpress=new ArrayList<ExpressBillManage>();
		maps.put("normal", normalExpress);
		maps.put("exception", exceptionExpress);
		for (int i = 0; i < expressBillManages.size(); i++) {
			ExpressBillManage expressBillManage = expressBillManages.get(i);
			ExpressBillManage oldGoodSku = expressBillManageDao.findOld(
					expressBillManage.getCustomerId(),
					expressBillManage.getExpressNo(),
					expressBillManage.getOrderNo());
			if (oldGoodSku != null) {
				if (overwrite) {
					BeanUtils.copyProperties(expressBillManage, oldGoodSku,
							new String[] { "id" });
					expressBillManage = oldGoodSku;
				} else {
					exceptionExpress.add(expressBillManage);
					continue;
				}
			}
			normalExpress.add(expressBillManage);
			expressBillManageDao.save(expressBillManage);
		}
    	return maps;
	}
	
	/**
	 * 获取导入的运单订单的集合
	 * @param expressBillManages
	 * @return
	 */
	public List<String> getExistParam(List<ExpressBillManage> expressBillManages) {
		List<String> expressList=new ArrayList<String>();
		for (ExpressBillManage expressBillManage : expressBillManages) {
			expressList.add(expressBillManage.getExpressNo()+expressBillManage.getOrderNo());
		}
		return expressList;
	}
	
	/**
	 * 删除表中已经存在的数据，根据运单号和订单号的组合
	 * @param expressBillManages
	 * @return
	 */
	@Transactional(rollbackFor=Exception.class,readOnly = false)
	public int deleteByexpress(List<ExpressBillManage> expressBillManages) {
	   return expressBillManageDao.deleteByexpress(getExistParam(expressBillManages));
	}
	
	/**
	 * 批次保存数据
	 * @param customerCode
	 * @param expressBillManages
	 * @param overwrite
	 * @return
	 */
	//FIXME 之后完善重复单据 update部分
	@Transactional(rollbackFor=Exception.class,readOnly = false)
	public int[] sava(String customerCode,List<ExpressBillManage> expressBillManages,boolean overwrite) {
		String [] sql=new String[expressBillManages.size()];
		for (int i = 0; i < expressBillManages.size(); i++) {
			ExpressBillManage eb = expressBillManages.get(i);
			sql[i]="insert into express_bill_manage(customer_id,customer_name,net_work,express_no,order_no,from_province,from_city,from_district,from_street"
					+ ",from_address,from_name,from_tele,to_province,to_city,to_district,to_street,to_address,to_name,to_tele,order_time,weight,volume,head_line)"
					+ "VALUES ('" +eb.getCustomerId() + "','" +eb.getCustomerName() + "','" +eb.getNetWork() + "','" +eb.getExpressNo() + "','" +eb.getOrderNo() + "'"
					+ ",'" +eb.getFromProvince() + "','" +eb.getFromCity() + "','" +eb.getFromDistrict() + "','" +eb.getFromStreet() + "','" +eb.getFromAddress() + "'"
					+ ",'" +eb.getFromName() + "','" +eb.getFromTele() + "','" +eb.getToProvince() + "','" +eb.getToCity() + "','" +eb.getToDistrict() + "'"
					+ ",'" +eb.getToStreet() + "','" +eb.getToAddress() + "','" +eb.getToName() + "','" +eb.getToTele() + "','" +DateUtils.formatDate(eb.getOrderTime()) + "'"
					+ ",'" +eb.getWeight() + "','" +eb.getVolume() + "','" +eb.getHeadLine() + "'"
					+ ")";
		}
		log.info("ExpressBillManageService--save--sql:"+sql[0]);
		return jdbcTemplate.batchUpdate(sql);
	}

	/**
	 * 分页查询快递单流量
	 * @param param
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public Page<ExpressBillManage> loadPageExpressBill(Map<String, Object> param, int pageNumber, int pageSize) {
		PageRequest pageRequest = new PageRequest(pageNumber - 1, pageSize);
		Specification<ExpressBillManage> spec = getWhereClause(param);
		Page<ExpressBillManage> page = this.expressBillManageDao.findAll(spec, pageRequest);
		return page;
	}
	/**
	 * 查询条件封装
	 * @param searchParams
	 * @return
	 */
	private Specification<ExpressBillManage> getWhereClause(final Map<String, Object> searchParams) {
		return new Specification<ExpressBillManage>() {
			@Override
			public Predicate toPredicate(Root<ExpressBillManage> r, CriteriaQuery<?> q, CriteriaBuilder cb) {
				Predicate pre = cb.conjunction();

				try {
					// 运单号
					String expressNo = (String) searchParams.get("expressNo");
					if (StringUtils.isNotBlank(expressNo)) {
						pre.getExpressions().add(cb.equal(r.get("expressNo"), expressNo));
					}
					// 订单号
					String orderNo = (String) searchParams.get("orderNo");
					if (StringUtils.isNotBlank(orderNo)) {
						pre.getExpressions().add(cb.equal(r.get("orderNo"), orderNo));
					}
					// 收件省
					String toProvince = (String) searchParams.get("toProvince");
					if (StringUtils.isNotBlank(toProvince)) {
						pre.getExpressions().add(cb.equal(r.get("toProvince"), toProvince));
					}
					// 收件姓名
					String toName = (String) searchParams.get("toName");
					if (StringUtils.isNotBlank(toName)) {
						pre.getExpressions().add(cb.equal(r.get("toName"), toName));
					}
					//商家姓名
					String customerName = (String) searchParams.get("customerName");
					if (StringUtils.isNotBlank(customerName)) {
						pre.getExpressions().add(cb.equal(r.get("customerName"), customerName));
					}
					// 创建时间
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
					String fromDateStr = (String) searchParams.get("fromDate");
					if (StringUtils.isNotBlank(fromDateStr)) {
						Date fromDate = df.parse(fromDateStr + " 00:00:00");
						pre.getExpressions().add(cb.greaterThan(r.<Date> get("orderTime"),fromDate));
					}
					String toDateStr = (String) searchParams.get("toDate");
					if (StringUtils.isNotBlank(toDateStr)) {
						Date toDate = df.parse(toDateStr + " 23:59:59");
						pre.getExpressions().add(cb.lessThan(r.<Date> get("orderTime"),toDate));
					}
				} catch (ParseException e) {
				}

				return pre;
			}
		};
	}
	/**
	 * 查询快递流量中的所有的商家姓名
	 * @return
	 */
	public List<Map<String, Object>> findCustomerNames() {
		String sql="select customer_name as customerName from express_bill_manage group by customer_name";
		return jdbcTemplate.queryForList(sql);
	}
	/**
	 * 查询快递流量中的所有的收件省
	 * @return
	 */
	public List<Map<String, Object>> findToProvinces() {
		String sql="select to_province as toProvince from express_bill_manage group by to_province";
		return jdbcTemplate.queryForList(sql);
	}
	/**
	 * 根据id查询快递流量
	 * @param parseLong
	 * @return
	 */
	public ExpressBillManage findOne(long parseLong) {
		return expressBillManageDao.findOne(parseLong);
	}
}
